/**
 * 💯 财务健康评分服务
 * 计算用户的财务健康度并提供改进建议
 */

const db = require('../config/database');
const { calculateStats, calculateCV } = require('../utils/stats');

class HealthScorer {
  /**
   * 计算综合健康评分
   */
  static async calculateHealthScore(userId, bookId, startDate, endDate) {
    try {
      // 1. 获取基础数据
      const baseData = await this.getBaseData(userId, bookId, startDate, endDate);
      
      // 2. 计算各维度得分
      const savingRateScore = this.calculateSavingRateScore(baseData);
      const rationalityScore = await this.calculateRationalityScore(userId, bookId, baseData);
      const executionScore = await this.calculateExecutionScore(userId, bookId, baseData);
      const stabilityScore = this.calculateStabilityScore(baseData);

      // 3. 计算总分
      const totalScore = savingRateScore + rationalityScore + executionScore + stabilityScore;

      // 4. 生成建议
      const suggestions = this.generateSuggestions({
        totalScore,
        savingRateScore,
        rationalityScore,
        executionScore,
        stabilityScore,
        baseData
      });

      return {
        total_score: totalScore,
        saving_rate_score: savingRateScore,
        rationality_score: rationalityScore,
        execution_score: executionScore,
        stability_score: stabilityScore,
        income: baseData.income,
        expense: baseData.expense,
        saving_rate: baseData.savingRate,
        budget_execution_rate: baseData.budgetExecutionRate || 0,
        expense_variance: baseData.expenseVariance,
        suggestions
      };
    } catch (error) {
      console.error('计算健康评分失败:', error);
      throw error;
    }
  }

  /**
   * 获取基础数据
   */
  static async getBaseData(userId, bookId, startDate, endDate) {
    // 收入支出统计
    const [summary] = await db.query(`
      SELECT 
        SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as income,
        SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as expense,
        COUNT(CASE WHEN type = 'expense' THEN 1 END) as expense_count
      FROM records
      WHERE user_id = ? AND book_id = ?
        AND DATE(date) BETWEEN ? AND ?
        AND is_deleted != 1
    `, [userId, bookId, startDate, endDate]);

    const income = parseFloat(summary[0]?.income) || 0;
    const expense = parseFloat(summary[0]?.expense) || 0;
    const savingRate = income > 0 ? ((income - expense) / income * 100) : 0;

    // 获取每日支出（用于计算方差）
    const [dailyExpenses] = await db.query(`
      SELECT DATE(date) as date, SUM(amount) as amount
      FROM records
      WHERE user_id = ? AND book_id = ? AND type = 'expense'
        AND DATE(date) BETWEEN ? AND ?
        AND is_deleted != 1
      GROUP BY DATE(date)
    `, [userId, bookId, startDate, endDate]);

    const dailyAmounts = dailyExpenses.map(d => parseFloat(d.amount));
    const stats = calculateStats(dailyAmounts);

    // 获取分类支出
    const [categoryExpenses] = await db.query(`
      SELECT category, SUM(amount) as amount
      FROM records
      WHERE user_id = ? AND book_id = ? AND type = 'expense'
        AND DATE(date) BETWEEN ? AND ?
        AND is_deleted != 1
      GROUP BY category
    `, [userId, bookId, startDate, endDate]);

    return {
      income,
      expense,
      savingRate,
      expenseCount: summary[0]?.expense_count || 0,
      dailyExpenses: dailyAmounts,
      expenseVariance: stats.variance,
      categoryExpenses
    };
  }

  /**
   * 计算储蓄率得分（满分30）
   */
  static calculateSavingRateScore(baseData) {
    const { savingRate } = baseData;
    
    // 评分标准
    if (savingRate >= 30) return 30;      // 储蓄率>=30%: 满分
    if (savingRate >= 20) return 25;      // 储蓄率>=20%: 25分
    if (savingRate >= 10) return 20;      // 储蓄率>=10%: 20分
    if (savingRate >= 5) return 15;       // 储蓄率>=5%: 15分
    if (savingRate > 0) return 10;        // 储蓄率>0%: 10分
    return 0;                              // 入不敷出: 0分
  }

  /**
   * 计算消费合理性得分（满分30）
   */
  static async calculateRationalityScore(userId, bookId, baseData) {
    const { expense, categoryExpenses } = baseData;
    
    if (expense === 0) return 30;

    // 合理的消费占比标准（根据中国居民消费标准）
    const standards = {
      '餐饮': { ideal: 30, max: 40 },
      '交通': { ideal: 10, max: 15 },
      '购物': { ideal: 20, max: 30 },
      '娱乐': { ideal: 10, max: 20 },
      '住房': { ideal: 30, max: 40 },
      '医疗': { ideal: 5, max: 10 },
      '教育': { ideal: 10, max: 20 },
      '其他': { ideal: 10, max: 20 }
    };

    let score = 30;
    let deductions = 0;

    for (const cat of categoryExpenses) {
      const ratio = (parseFloat(cat.amount) / expense) * 100;
      const standard = standards[cat.category];
      
      if (standard && ratio > standard.max) {
        // 超过最大值，扣分
        const excess = ratio - standard.max;
        deductions += excess * 0.2; // 每超1%扣0.2分
      }
    }

    score = Math.max(0, score - deductions);
    return Math.round(score * 10) / 10;
  }

  /**
   * 计算预算执行力得分（满分20）
   */
  static async calculateExecutionScore(userId, bookId, baseData) {
    try {
      // 获取预算
      const [budgets] = await db.query(`
        SELECT SUM(amount) as total_budget
        FROM budgets
        WHERE user_id = ? AND book_id = ?
      `, [userId, bookId]);

      if (!budgets[0] || !budgets[0].total_budget) {
        baseData.budgetExecutionRate = null;
        return 20; // 无预算时给满分
      }

      const budget = parseFloat(budgets[0].total_budget);
      const actual = baseData.expense;
      const executionRate = (actual / budget) * 100;
      
      baseData.budgetExecutionRate = executionRate;

      // 评分标准：执行率在85-95%之间为最佳
      if (executionRate <= 95 && executionRate >= 85) return 20;
      if (executionRate < 85) {
        const diff = 85 - executionRate;
        return Math.max(0, 20 - diff * 0.5);
      }
      if (executionRate > 95) {
        const diff = executionRate - 95;
        return Math.max(0, 20 - diff * 0.5);
      }

      return 20;
    } catch (error) {
      console.error('计算执行力得分失败:', error);
      return 20;
    }
  }

  /**
   * 计算消费稳定性得分（满分20）
   */
  static calculateStabilityScore(baseData) {
    const { dailyExpenses } = baseData;
    
    if (dailyExpenses.length < 5) return 20; // 数据不足给满分

    // 计算变异系数
    const cv = calculateCV(dailyExpenses);

    // 评分标准：变异系数越小越稳定
    if (cv <= 30) return 20;               // CV<=30%: 满分
    if (cv <= 50) return 15;               // CV<=50%: 15分
    if (cv <= 70) return 10;               // CV<=70%: 10分
    if (cv <= 100) return 5;               // CV<=100%: 5分
    return 0;                              // CV>100%: 0分
  }

  /**
   * 生成改进建议（增强版）
   * 包含具体金额建议、预算设置、储蓄目标等
   */
  static generateSuggestions(scoreData) {
    const suggestions = [];
    const { savingRateScore, rationalityScore, executionScore, stabilityScore, baseData } = scoreData;

    // 储蓄率建议（详细版）
    if (savingRateScore < 20) {
      // 计算具体建议金额
      const targetSavingRate = 0.20; // 目标储蓄率20%
      const targetSaving = baseData.income * targetSavingRate;
      const currentSaving = baseData.income - baseData.expense;
      const needToSave = targetSaving - currentSaving;
      
      const savingAdvice = needToSave > 0 
        ? `建议每月增加储蓄¥${needToSave.toFixed(2)}，可通过减少非必需支出实现。`
        : `当前储蓄较少，建议设定每月储蓄目标：¥${targetSaving.toFixed(2)}。`;
      
      suggestions.push({
        type: 'saving_rate',
        priority: 'high',
        title: '💰 储蓄率偏低',
        content: `当前储蓄率${(baseData.savingRate || 0).toFixed(1)}%，建议提高至20%以上。\n\n${savingAdvice}\n\n📋 改进建议：\n1. 减少非必需支出（娱乐、购物等）\n2. 设定每月储蓄目标：¥${targetSaving.toFixed(0)}\n3. 采用"先储蓄，后消费"原则\n4. 使用零钱通/余额宝等理财工具`,
        icon: '💰',
        targetAmount: targetSaving,
        currentAmount: currentSaving
      });
    } else if (savingRateScore < 25 && baseData.income > 0) {
      // 储蓄率一般，给予鼓励和提升建议
      const targetSaving = baseData.income * 0.30; // 目标30%
      suggestions.push({
        type: 'saving_rate',
        priority: 'medium',
        title: '💰 储蓄习惯良好，可进一步提升',
        content: `当前储蓄率${(baseData.savingRate || 0).toFixed(1)}%，表现不错！\n\n尝试挑战更高目标：每月储蓄¥${targetSaving.toFixed(0)}（30%）\n\n📈 提升建议：\n1. 尝试定期储蓄计划\n2. 考虑购买理财产品\n3. 减少冲动消费`,
        icon: '💰'
      });
    }

    // 预算管理建议（新增）
    if (executionScore < 15) {
      // 如果有预算数据
      if (baseData.budgetExecutionRate !== null) {
        const executionRate = baseData.budgetExecutionRate;
        let adviceContent = '';
        
        if (executionRate > 110) {
          const overSpent = baseData.expense - (baseData.expense / (executionRate / 100));
          adviceContent = `当前预算执行率${executionRate.toFixed(1)}%，超支约¥${overSpent.toFixed(2)}。\n\n📋 建议调整预算：\n`;
          
          // 根据支出结构给出具体建议
          if (baseData.expense > 2000) {
            adviceContent += `1. 餐饮预算：¥${(baseData.expense * 0.35).toFixed(0)}/月\n`;
            adviceContent += `2. 购物预算：¥${(baseData.expense * 0.25).toFixed(0)}/月\n`;
            adviceContent += `3. 交通预算：¥${(baseData.expense * 0.10).toFixed(0)}/月\n`;
            adviceContent += `4. 其他预算：¥${(baseData.expense * 0.30).toFixed(0)}/月`;
          }
        } else if (executionRate < 80) {
          adviceContent = `预算执行率较低（${executionRate.toFixed(1)}%），预算可能设置过高。\n\n建议根据实际支出调整预算，提高预算的指导作用。`;
        }
        
        suggestions.push({
          type: 'budget',
          priority: 'medium',
          title: '🎯 预算管理',
          content: adviceContent,
          icon: '🎯'
        });
      } else {
        // 没有预算，建议创建
        suggestions.push({
          type: 'budget',
          priority: 'high',
          title: '🎯 建议创建预算',
          content: `您还未设置预算，建议根据近期消费情况创建预算：\n\n📊 推荐预算分配：\n1. 餐饮：¥${(baseData.expense * 0.40).toFixed(0)}/月（40%）\n2. 购物：¥${(baseData.expense * 0.25).toFixed(0)}/月（25%）\n3. 交通：¥${(baseData.expense * 0.10).toFixed(0)}/月（10%）\n4. 娱乐：¥${(baseData.expense * 0.15).toFixed(0)}/月（15%）\n5. 其他：¥${(baseData.expense * 0.10).toFixed(0)}/月（10%）\n\n总预算建议：¥${baseData.expense.toFixed(0)}/月`,
          icon: '🎯',
          recommendedBudget: {
            total: baseData.expense,
            categories: {
              '餐饮': baseData.expense * 0.40,
              '购物': baseData.expense * 0.25,
              '交通': baseData.expense * 0.10,
              '娱乐': baseData.expense * 0.15,
              '生活': baseData.expense * 0.10
            }
          }
        });
      }
    }

    // 消费结构优化建议
    if (rationalityScore < 20) {
      suggestions.push({
        type: 'rationality',
        priority: 'medium',
        title: '📊 消费结构需优化',
        content: `部分类别支出占比不够合理，建议调整：\n\n💡 优化建议：\n1. 分析高支出类别的必要性\n2. 减少非必需类别（娱乐、购物）\n3. 寻找替代方案降低成本\n4. 设定分类消费上限\n\n🎯 标准占比参考：\n• 必需支出（餐饮、交通）：60-70%\n• 娱乐购物：20-30%\n• 储蓄：10-20%`,
        icon: '📊'
      });
    }

    // 支出稳定性建议
    if (stabilityScore < 15) {
      const avgDailyExpense = baseData.expense / 30; // 假设30天
      suggestions.push({
        type: 'stability',
        priority: 'low',
        title: '📈 消费波动较大',
        content: `日常消费不够稳定，波动较大。\n\n建议每日消费控制在¥${avgDailyExpense.toFixed(2)}左右。\n\n💡 稳定消费的方法：\n1. 规律化日常开支（固定时间、固定地点）\n2. 避免冲动消费（24小时冷静期）\n3. 建立消费缓冲金（¥${(baseData.expense * 0.2).toFixed(0)}）\n4. 大额支出提前规划`,
        icon: '📈',
        recommendedDailyLimit: avgDailyExpense
      });
    }

    // 减少小额高频支出建议（新增）
    if (baseData.expense > 0) {
      // 如果有很多小额支出的情况（可以根据实际数据调整逻辑）
      suggestions.push({
        type: 'small_expenses',
        priority: 'low',
        title: '💡 减少零散开支',
        content: `建议整合小额支出，减少不必要的服务费：\n\n🎯 优化建议：\n1. 零钱提现：累计较大金额再提现，减少手续费\n2. 外卖订单：合并订单，减少配送费\n3. 共享单车：考虑购买月卡更划算\n4. 视频会员：年费比月费更优惠`,
        icon: '💡'
      });
    }

    // 如果得分很高，给予鼓励和进阶建议
    if (scoreData.totalScore >= 80) {
      const advancedSaving = baseData.income * 0.30;
      suggestions.push({
        type: 'encouragement',
        priority: 'low',
        title: '🎉 财务状况良好',
        content: `您的财务管理做得很好！综合得分${scoreData.totalScore.toFixed(1)}分。\n\n🚀 进阶建议：\n1. 尝试定期储蓄：每月¥${advancedSaving.toFixed(0)}（30%）\n2. 考虑投资理财（低风险产品）\n3. 学习更多财务知识\n4. 建立紧急备用金（3-6个月支出）`,
        icon: '🎉'
      });
    } else if (scoreData.totalScore >= 60) {
      suggestions.push({
        type: 'encouragement',
        priority: 'low',
        title: '👍 财务状况正常',
        content: `综合得分${scoreData.totalScore.toFixed(1)}分，财务状况正常。\n\n继续保持良好习惯，关注薄弱环节的改进。`,
        icon: '👍'
      });
    }

    return suggestions;
  }

  /**
   * 保存评分记录
   */
  static async saveScore(userId, bookId, scoreDate, scoreData) {
    try {
      await db.query(`
        INSERT INTO health_scores 
        (user_id, book_id, score_date, total_score, saving_rate_score,
         rationality_score, execution_score, stability_score, income, expense,
         saving_rate, budget_execution_rate, expense_variance, suggestions)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
          total_score = VALUES(total_score),
          saving_rate_score = VALUES(saving_rate_score),
          rationality_score = VALUES(rationality_score),
          execution_score = VALUES(execution_score),
          stability_score = VALUES(stability_score),
          income = VALUES(income),
          expense = VALUES(expense),
          saving_rate = VALUES(saving_rate),
          budget_execution_rate = VALUES(budget_execution_rate),
          expense_variance = VALUES(expense_variance),
          suggestions = VALUES(suggestions)
      `, [
        userId, bookId, scoreDate,
        scoreData.total_score,
        scoreData.saving_rate_score,
        scoreData.rationality_score,
        scoreData.execution_score,
        scoreData.stability_score,
        scoreData.income,
        scoreData.expense,
        scoreData.saving_rate,
        scoreData.budget_execution_rate,
        scoreData.expense_variance,
        JSON.stringify(scoreData.suggestions)
      ]);
    } catch (error) {
      console.error('保存评分记录失败:', error);
      throw error;
    }
  }
}

module.exports = HealthScorer;

